--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: t_ata; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_ata (
    id integer NOT NULL,
    mac character(12) NOT NULL,
    line_count integer DEFAULT 2 NOT NULL,
    secret character varying(20) NOT NULL,
    CONSTRAINT "Length of MAC Address must be 12 characters." CHECK ((length(mac) = 12)),
    CONSTRAINT "Length of the shared secret must be at least 10 characters." CHECK ((length((secret)::text) >= 10)),
    CONSTRAINT "Line Count is must be larger than 0." CHECK ((line_count > 0)),
    CONSTRAINT "Line Count is must be less than 3." CHECK ((line_count < 3)),
    CONSTRAINT "MAC address must be all caps, containing only, 0-9 and A-F." CHECK ((mac ~ '^[0-9A-F]+$'::text)),
    CONSTRAINT "The shared secret cannot contain whitespace." CHECK (((secret)::text !~ '[\\s]'::text))
);


ALTER TABLE public.t_ata OWNER TO postgres;

--
-- Name: ata_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE ata_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.ata_id_seq OWNER TO postgres;

--
-- Name: ata_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE ata_id_seq OWNED BY t_ata.id;


--
-- Name: t_did; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_did (
    id integer NOT NULL,
    did bigint NOT NULL,
    ata character(12) NOT NULL,
    line integer NOT NULL,
    cid character varying(15) NOT NULL,
    record boolean DEFAULT false NOT NULL,
    ring_seconds integer DEFAULT 30 NOT NULL,
    CONSTRAINT "CID may only contain ""0-9A-Z A-z,.!?""." CHECK (((cid)::text ~ '^[0-9 A-Za-z,\\.\\!\\?]+$'::text)),
    CONSTRAINT "DID Length Must be 10 or 15." CHECK (((length((did)::text) = 15) OR (length((did)::text) = 10))),
    CONSTRAINT "Line must be larger than 0." CHECK ((line > 0)),
    CONSTRAINT "Line must be less than 3." CHECK ((line < 3)),
    CONSTRAINT "Ring seconds must be at least 10." CHECK ((ring_seconds >= 10))
);


ALTER TABLE public.t_did OWNER TO postgres;

--
-- Name: phone_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE phone_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.phone_id_seq OWNER TO postgres;

--
-- Name: phone_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE phone_id_seq OWNED BY t_did.id;


--
-- Name: t_cdr; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_cdr (
    calldate timestamp with time zone DEFAULT now() NOT NULL,
    clid character varying(80) NOT NULL,
    src character varying(80) NOT NULL,
    dst character varying(80) NOT NULL,
    dcontext character varying(80) NOT NULL,
    channel character varying(80) NOT NULL,
    dstchannel character varying(80) NOT NULL,
    lastapp character varying(80) NOT NULL,
    lastdata character varying(80) NOT NULL,
    duration bigint DEFAULT 0 NOT NULL,
    billsec bigint DEFAULT 0 NOT NULL,
    disposition character varying(45) NOT NULL,
    amaflags bigint DEFAULT 0 NOT NULL,
    accountcode character varying(20) NOT NULL,
    uniqueid character varying(32) NOT NULL,
    userfield character varying(255) NOT NULL,
    destination character varying(80)
);


ALTER TABLE public.t_cdr OWNER TO postgres;

--
-- Name: t_did_outbound; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_did_outbound (
    id integer NOT NULL,
    did bigint NOT NULL,
    sip_channel bigint NOT NULL
);


ALTER TABLE public.t_did_outbound OWNER TO postgres;

--
-- Name: t_did_outbound_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE t_did_outbound_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t_did_outbound_id_seq OWNER TO postgres;

--
-- Name: t_did_outbound_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE t_did_outbound_id_seq OWNED BY t_did_outbound.id;


--
-- Name: t_general; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_general (
    id integer NOT NULL,
    key character varying NOT NULL,
    value_i integer,
    value_b boolean
);


ALTER TABLE public.t_general OWNER TO postgres;

--
-- Name: t_general_i_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE t_general_i_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t_general_i_id_seq OWNER TO postgres;

--
-- Name: t_general_i_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE t_general_i_id_seq OWNED BY t_general.id;


--
-- Name: t_outbound_sip; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_outbound_sip (
    id integer NOT NULL,
    host character varying(80) NOT NULL,
    username character varying(80) NOT NULL,
    name character varying(80) NOT NULL,
    port integer DEFAULT 5060 NOT NULL,
    secret character varying NOT NULL
);


ALTER TABLE public.t_outbound_sip OWNER TO postgres;

--
-- Name: t_outbound_sip_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE t_outbound_sip_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t_outbound_sip_id_seq OWNER TO postgres;

--
-- Name: t_outbound_sip_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE t_outbound_sip_id_seq OWNED BY t_outbound_sip.id;


--
-- Name: t_simultaneous_ring; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_simultaneous_ring (
    id integer NOT NULL,
    did integer NOT NULL,
    remote_ani integer NOT NULL
);


ALTER TABLE public.t_simultaneous_ring OWNER TO postgres;

--
-- Name: t_simultaneous_ring_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE t_simultaneous_ring_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t_simultaneous_ring_id_seq OWNER TO postgres;

--
-- Name: t_simultaneous_ring_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE t_simultaneous_ring_id_seq OWNED BY t_simultaneous_ring.id;


--
-- Name: t_vm; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE t_vm (
    id integer NOT NULL,
    did integer NOT NULL,
    password integer DEFAULT 123456 NOT NULL,
    vmname character varying(80) DEFAULT 'Voicemail User'::character varying NOT NULL,
    email character varying(120) NOT NULL,
    delete boolean DEFAULT false NOT NULL
);


ALTER TABLE public.t_vm OWNER TO postgres;

--
-- Name: t_vm_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE t_vm_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t_vm_id_seq OWNER TO postgres;

--
-- Name: t_vm_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE t_vm_id_seq OWNED BY t_vm.id;


--
-- Name: v_calls; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW v_calls AS
    SELECT t_cdr.uniqueid, t_cdr.dst, t_cdr.calldate, t_cdr.clid, t_did.did, t_cdr.disposition, t_cdr.lastapp, t_cdr.duration, t_cdr.billsec FROM t_cdr, t_did WHERE (t_did.did = (t_cdr.accountcode)::bigint) ORDER BY t_did.did, t_cdr.calldate;


ALTER TABLE public.v_calls OWNER TO postgres;

--
-- Name: v_did_outbound; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW v_did_outbound AS
    SELECT t_outbound_sip.name, t_did_outbound.did, t_outbound_sip.host, t_outbound_sip.port, t_outbound_sip.username, t_outbound_sip.secret FROM t_did_outbound, t_outbound_sip WHERE (t_did_outbound.sip_channel = t_outbound_sip.id);


ALTER TABLE public.v_did_outbound OWNER TO postgres;

--
-- Name: v_sip_int_users; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW v_sip_int_users AS
    SELECT t_did.did, t_did.cid, t_did.ata, t_did.line, t_vm.did AS mailbox, t_ata.secret FROM ((t_did LEFT JOIN t_vm ON ((t_did.did = t_vm.did))) LEFT JOIN t_ata ON ((t_did.ata = t_ata.mac)));


ALTER TABLE public.v_sip_int_users OWNER TO postgres;

--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_ata ALTER COLUMN id SET DEFAULT nextval('ata_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_did ALTER COLUMN id SET DEFAULT nextval('phone_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_did_outbound ALTER COLUMN id SET DEFAULT nextval('t_did_outbound_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_general ALTER COLUMN id SET DEFAULT nextval('t_general_i_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_outbound_sip ALTER COLUMN id SET DEFAULT nextval('t_outbound_sip_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_simultaneous_ring ALTER COLUMN id SET DEFAULT nextval('t_simultaneous_ring_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE t_vm ALTER COLUMN id SET DEFAULT nextval('t_vm_id_seq'::regclass);


--
-- Name: ata_MAC_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_ata
    ADD CONSTRAINT "ata_MAC_key" UNIQUE (mac);


--
-- Name: ata_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_ata
    ADD CONSTRAINT ata_id_key UNIQUE (id);


--
-- Name: phone_did_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_did
    ADD CONSTRAINT phone_did_key UNIQUE (did);


--
-- Name: phone_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_did
    ADD CONSTRAINT phone_pkey PRIMARY KEY (id);


--
-- Name: t_did_outbound_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_did_outbound
    ADD CONSTRAINT t_did_outbound_pkey PRIMARY KEY (id);


--
-- Name: t_general_i_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_general
    ADD CONSTRAINT t_general_i_pkey PRIMARY KEY (id);


--
-- Name: t_outbound_sip_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_outbound_sip
    ADD CONSTRAINT t_outbound_sip_name_key UNIQUE (name);


--
-- Name: t_outbound_sip_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_outbound_sip
    ADD CONSTRAINT t_outbound_sip_pkey PRIMARY KEY (id);


--
-- Name: t_simultaneous_ring_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_simultaneous_ring
    ADD CONSTRAINT t_simultaneous_ring_pkey PRIMARY KEY (id);


--
-- Name: t_vm_did_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_vm
    ADD CONSTRAINT t_vm_did_key UNIQUE (did);


--
-- Name: t_vm_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY t_vm
    ADD CONSTRAINT t_vm_pkey PRIMARY KEY (id);


--
-- Name: t_did_ata_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY t_did
    ADD CONSTRAINT t_did_ata_fkey FOREIGN KEY (ata) REFERENCES t_ata(mac);


--
-- Name: t_did_outbound_did_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY t_did_outbound
    ADD CONSTRAINT t_did_outbound_did_fkey FOREIGN KEY (did) REFERENCES t_did(did);


--
-- Name: t_did_outbound_sip_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY t_did_outbound
    ADD CONSTRAINT t_did_outbound_sip_channel_fkey FOREIGN KEY (sip_channel) REFERENCES t_outbound_sip(id);


--
-- Name: t_simultaneous_ring_did_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY t_simultaneous_ring
    ADD CONSTRAINT t_simultaneous_ring_did_fkey FOREIGN KEY (did) REFERENCES t_did(did);


--
-- Name: t_vm_did_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY t_vm
    ADD CONSTRAINT t_vm_did_fkey FOREIGN KEY (did) REFERENCES t_did(did);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- Name: t_ata; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_ata FROM PUBLIC;
REVOKE ALL ON TABLE t_ata FROM postgres;
GRANT ALL ON TABLE t_ata TO postgres;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE t_ata TO asterisk;


--
-- Name: ata_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE ata_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE ata_id_seq FROM postgres;
GRANT ALL ON SEQUENCE ata_id_seq TO postgres;
GRANT ALL ON SEQUENCE ata_id_seq TO asterisk;


--
-- Name: t_did; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_did FROM PUBLIC;
REVOKE ALL ON TABLE t_did FROM postgres;
GRANT ALL ON TABLE t_did TO postgres;
GRANT SELECT ON TABLE t_did TO asterisk;


--
-- Name: t_cdr; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_cdr FROM PUBLIC;
REVOKE ALL ON TABLE t_cdr FROM postgres;
GRANT ALL ON TABLE t_cdr TO postgres;
GRANT SELECT,INSERT ON TABLE t_cdr TO asterisk;


--
-- Name: t_general; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_general FROM PUBLIC;
REVOKE ALL ON TABLE t_general FROM postgres;
GRANT ALL ON TABLE t_general TO postgres;
GRANT SELECT,UPDATE ON TABLE t_general TO asterisk;


--
-- Name: t_outbound_sip; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_outbound_sip FROM PUBLIC;
REVOKE ALL ON TABLE t_outbound_sip FROM postgres;
GRANT ALL ON TABLE t_outbound_sip TO postgres;
GRANT SELECT ON TABLE t_outbound_sip TO asterisk;


--
-- Name: t_simultaneous_ring; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_simultaneous_ring FROM PUBLIC;
REVOKE ALL ON TABLE t_simultaneous_ring FROM postgres;
GRANT ALL ON TABLE t_simultaneous_ring TO postgres;
GRANT SELECT ON TABLE t_simultaneous_ring TO asterisk;


--
-- Name: t_vm; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE t_vm FROM PUBLIC;
REVOKE ALL ON TABLE t_vm FROM postgres;
GRANT ALL ON TABLE t_vm TO postgres;
GRANT SELECT ON TABLE t_vm TO asterisk;


--
-- Name: v_calls; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE v_calls FROM PUBLIC;
REVOKE ALL ON TABLE v_calls FROM postgres;
GRANT ALL ON TABLE v_calls TO postgres;
GRANT SELECT ON TABLE v_calls TO asterisk;


--
-- Name: v_did_outbound; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE v_did_outbound FROM PUBLIC;
REVOKE ALL ON TABLE v_did_outbound FROM postgres;
GRANT ALL ON TABLE v_did_outbound TO postgres;
GRANT SELECT ON TABLE v_did_outbound TO asterisk;


--
-- Name: v_sip_int_users; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE v_sip_int_users FROM PUBLIC;
REVOKE ALL ON TABLE v_sip_int_users FROM postgres;
GRANT ALL ON TABLE v_sip_int_users TO postgres;
GRANT SELECT ON TABLE v_sip_int_users TO asterisk;


--
-- PostgreSQL database dump complete
--

